from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import Ui_pelangganEdit

KODE,  NAMA,  NOMOR,  PER,  KET = range(5)

class PelangganEdit(QDialog, Ui_pelangganEdit.Ui_pelangganEditDlg):
    
  StyleSheet = """
QLineEdit[mandatory="true"] {
    background-color: rgb(255, 255, 127);
    color: darkblue;
}
"""

  def __init__(self, dbConn, tipe,  dataGlob, parent=None):
        super(PelangganEdit, self).__init__(parent)
        self.setupUi(self)
        self.db = dbConn
        self.tipe = tipe
        self.dataGlob = dataGlob
        self.initData()
        
        self.idModel = QSqlTableModel(self)
        self.idModel.setTable("no_akhir")
        self.idModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        
        self.mastModel = QSqlTableModel(self)
        self.mastModel.setTable("pelanggan")
        self.mastModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        
        self.lineedits = (self.noMmc, self.nama,self.alamat, self.ktp)
        for lineEdit in self.lineedits:
            lineEdit.setProperty("mandatory", QVariant(True))
        self.setStyleSheet(PelangganEdit.StyleSheet)
        
  @pyqtSignature("")
  def on_simpan_clicked(self):
      if self.tipe == "add":
          if self.tambahData():
              self.close()
          else:
              QMessageBox.warning(self, "Pelanggan","Tidak berhasil menambah data.")
      else:
          if self.updateData():
              self.close()
          else:
              QMessageBox.warning(self, "Pelanggan","Data tidak berhasil disimpan.")
      
  @pyqtSignature("")
  def on_batal_clicked(self):
      self.close()

  def tambahData(self):
      if not self.validasi():
          QMessageBox.warning(self, "Pelanggan","Data yang diinput tidak valid / Kolom kosong.")
          return
      id = self.idBaru()
      self.idPlg.setText(id)
      self.kodePlg.setText(self.kodeBaru())
      self.auditTime.setDateTime(QDateTime.currentDateTime())
      tgl = QDate.currentDate().toString("yyyy-MM-dd")
      self.mastModel.setFilter("tgl_daftar = '"+tgl+"'")
      self.mastModel.select()      
      QSqlDatabase.database().transaction()
      if not self.mastModel.insertRow(0):
          return
      self.mastModel.setData(self.mastModel.index(0, 0), QVariant(self.idPlg.text()))
      self.mastModel.setData(self.mastModel.index(0, 1), QVariant(self.kodePlg.text()))
      self.mastModel.setData(self.mastModel.index(0, 2), QVariant(self.nama.text()))
      self.mastModel.setData(self.mastModel.index(0, 3), QVariant(self.alamat.text()))
      self.mastModel.setData(self.mastModel.index(0, 4), QVariant(self.kota.text()))
      self.mastModel.setData(self.mastModel.index(0, 5), QVariant(self.kodePos.text()))
      self.mastModel.setData(self.mastModel.index(0, 6), QVariant(self.telp.text()))
      self.mastModel.setData(self.mastModel.index(0, 7), QVariant(self.fax.text()))
      self.mastModel.setData(self.mastModel.index(0, 8), QVariant(self.alamat2.text()))
      self.mastModel.setData(self.mastModel.index(0, 9), QVariant(self.kota2.text()))
      self.mastModel.setData(self.mastModel.index(0, 10), QVariant(self.kodePos2.text()))
      self.mastModel.setData(self.mastModel.index(0, 11), QVariant(self.telp2.text()))
      self.mastModel.setData(self.mastModel.index(0, 12), QVariant(self.fax2.text()))
      self.mastModel.setData(self.mastModel.index(0, 13), QVariant(self.hp.text()))
      self.mastModel.setData(self.mastModel.index(0, 14), QVariant(self.email.text()))
      self.mastModel.setData(self.mastModel.index(0, 15), QVariant(self.noMmc.text()))
      self.mastModel.setData(self.mastModel.index(0, 16), QVariant(self.ktp.text()))
      self.mastModel.setData(self.mastModel.index(0, 17), QVariant(self.npwp.text()))
      self.mastModel.setData(self.mastModel.index(0, 18), QVariant(""))
      self.mastModel.setData(self.mastModel.index(0, 19), QVariant(self.auditUser.text()))
      self.mastModel.setData(self.mastModel.index(0, 20), QVariant(self.tglDaftar.date()))
      self.mastModel.setData(self.mastModel.index(0, 21), QVariant(self.auditTime.dateTime()))
      self.mastModel.setData(self.mastModel.index(0, 22), QVariant(self.jnsKel.currentText().left(1)))
      self.mastModel.setData(self.mastModel.index(0, 23), QVariant(self.tglLahir.date()))
      self.mastModel.setData(self.mastModel.index(0, 24), QVariant(self.bidangPekj.text()))
      self.mastModel.setData(self.mastModel.index(0, 25), QVariant(self.pekerjaan.text()))
      if not self.mastModel.submitAll():
          return
      if not QSqlDatabase.database().commit():
          return
      return True
#      query.prepare("INSERT INTO pelanggan (id_pelanggan, kode_pelanggan, nama, alamat1, kota1, kodepos1, telp1, fax1, alamat2, kota2, kodepos2, telp2, fax2, hp, email, no_kartu, no_ktp, npwp, ket, audit_user, tgl_daftar, audit_tgl, jk, tgl_lahir, bidang, pekerjaan ) VALUES (:id_pelanggan, :kode_pelanggan, :nama, :alamat1, :kota1, :kodepos1, :telp1, :fax1, :alamat2, :kota2, :kodepos2, :telp2, :fax2, :hp, :email, :no_kartu, :no_ktp, :npwp, :ket, :audit_user, :tgl_daftar, :audit_tgl, :jk, :tgl_lahir, :bidang, :pekerjaan)")      

  def updateData(self):
      if not self.validasi():
          QMessageBox.warning(self, "Pelanggan","Data yang diinput tidak valid / Kolom kosong.")
          return
      self.mastModel.setFilter("id_pelanggan = '"+self.tipe+"'")
      self.mastModel.select()      
      QSqlDatabase.database().transaction()
      self.mastModel.setData(self.mastModel.index(0, 2), QVariant(self.nama.text()))
      self.mastModel.setData(self.mastModel.index(0, 3), QVariant(self.alamat.text()))
      self.mastModel.setData(self.mastModel.index(0, 4), QVariant(self.kota.text()))
      self.mastModel.setData(self.mastModel.index(0, 5), QVariant(self.kodePos.text()))
      self.mastModel.setData(self.mastModel.index(0, 6), QVariant(self.telp.text()))
      self.mastModel.setData(self.mastModel.index(0, 7), QVariant(self.fax.text()))
      self.mastModel.setData(self.mastModel.index(0, 8), QVariant(self.alamat2.text()))
      self.mastModel.setData(self.mastModel.index(0, 9), QVariant(self.kota2.text()))
      self.mastModel.setData(self.mastModel.index(0, 10), QVariant(self.kodePos2.text()))
      self.mastModel.setData(self.mastModel.index(0, 11), QVariant(self.telp2.text()))
      self.mastModel.setData(self.mastModel.index(0, 12), QVariant(self.fax2.text()))
      self.mastModel.setData(self.mastModel.index(0, 13), QVariant(self.hp.text()))
      self.mastModel.setData(self.mastModel.index(0, 14), QVariant(self.email.text()))
      self.mastModel.setData(self.mastModel.index(0, 15), QVariant(self.noMmc.text()))
      self.mastModel.setData(self.mastModel.index(0, 16), QVariant(self.ktp.text()))
      self.mastModel.setData(self.mastModel.index(0, 17), QVariant(self.npwp.text()))
      self.mastModel.setData(self.mastModel.index(0, 18), QVariant(""))
      self.mastModel.setData(self.mastModel.index(0, 20), QVariant(self.tglDaftar.date()))
      self.mastModel.setData(self.mastModel.index(0, 22), QVariant(self.jnsKel.currentText().left(1)))
      self.mastModel.setData(self.mastModel.index(0, 23), QVariant(self.tglLahir.date()))
      self.mastModel.setData(self.mastModel.index(0, 24), QVariant(self.bidangPekj.text()))
      self.mastModel.setData(self.mastModel.index(0, 25), QVariant(self.pekerjaan.text()))
      if not self.mastModel.submitAll():
          return
      if not QSqlDatabase.database().commit():
          return
      return True

  def initData(self):
      hari = QDate.currentDate()
      self.tglDaftar.setDate(hari)
      self.auditUser.setText(self.dataGlob["nama"])
      if self.tipe != "add":
          query = QSqlQuery(self.db)
          qStr = "select id_pelanggan, kode_pelanggan, nama, alamat1, kota1, kodepos1, telp1, fax1, alamat2, kota2, kodepos2, telp2, fax2, hp, email, no_kartu, no_ktp, npwp, ket, audit_user, tgl_daftar, audit_tgl, jk, tgl_lahir, bidang, pekerjaan from pelanggan where id_pelanggan = '"+self.tipe+"'"
          query.exec_(qStr)
          query.next()
          self.idPlg.setText(unicode(query.value(0).toString()))
          self.kodePlg.setText(unicode(query.value(1).toString()))
          self.nama.setText(unicode(query.value(2).toString()))
          self.alamat.setText(unicode(query.value(3).toString()))
          self.kota.setText(unicode(query.value(4).toString()))
          self.kodePos.setText(unicode(query.value(5).toString()))
          self.telp.setText(unicode(query.value(6).toString()))
          self.fax.setText(unicode(query.value(7).toString()))
          self.alamat2.setText(unicode(query.value(8).toString()))
          self.kota2.setText(unicode(query.value(9).toString()))
          self.kodePos2.setText(unicode(query.value(10).toString()))
          self.telp2.setText(unicode(query.value(11).toString()))
          self.fax2.setText(unicode(query.value(12).toString()))
          self.hp.setText(unicode(query.value(13).toString()))
          self.email.setText(unicode(query.value(14).toString()))
          self.noMmc.setText(unicode(query.value(15).toString()))
          self.ktp.setText(unicode(query.value(16).toString()))
          self.npwp.setText(unicode(query.value(17).toString()))
          self.auditUser.setText(unicode(query.value(19).toString()))
          self.tglDaftar.setDate(query.value(20).toDate())
          self.auditTime.setDateTime(query.value(21).toDateTime())
          idx = 0
          if unicode(query.value(22).toString()) == "P":
              idx = 1
          self.jnsKel.setCurrentIndex(idx)
          self.tglLahir.setDate(query.value(23).toDate())
          self.bidangPekj.setText(unicode(query.value(24).toString()))
          self.pekerjaan.setText(unicode(query.value(25).toString()))

  def idBaru(self):
      noAkhir = 0
      kode = "PLG"
      per = QDate.currentDate().toString("MMyy")
      self.idModel.setFilter("kode_no = '"+kode+"' and periode = '"+per+"'")
      self.idModel.select()      
      class DatabaseError(Exception): pass
      rback = False
      try:
          if not QSqlDatabase.database().transaction():
              raise DatabaseError
          rback = True
          if self.idModel.rowCount() == 1:
              record = self.idModel.record(0)
              noAkhir = record.value(2).toInt()[0]
              noAkhir += 1
              self.idModel.setData(self.idModel.index(0, 2), QVariant(noAkhir))
          else:
              self.idModel.insertRow(0)
              self.idModel.setData(self.idModel.index(0, 0), QVariant(kode))
              self.idModel.setData(self.idModel.index(0, 1), QVariant("pelanggan"))
              self.idModel.setData(self.idModel.index(0, 2), QVariant("1"))
              self.idModel.setData(self.idModel.index(0, 3), QVariant(per))
              self.idModel.setData(self.idModel.index(0, 4), QVariant(""))
              noAkhir = 1
          self.idModel.submitAll()
          if not QSqlDatabase.database().commit():
            raise DatabaseError
          rback = False
      finally:
          if rback:
              if not QSqlDatabase.database().rollback():
                  raise DatabaseError        
                  
      idPlg = "PLG"+per+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      return idPlg

  def kodeBaru(self):
      noAkhir = 0
      query = QSqlQuery(self.db)
      kode = "CUS"
      per = QDate.currentDate().toString("MMyy")
      self.idModel.setFilter("kode_no = '"+kode+"' and periode = '"+per+"'")
      self.idModel.select()
      class DatabaseError(Exception): pass
      rback = False
      try:
          if not QSqlDatabase.database().transaction():
              raise DatabaseError
          rback = True
          if self.idModel.rowCount() == 1:
              record = self.idModel.record(0)
              noAkhir = record.value(2).toInt()[0]
              noAkhir += 1
              self.idModel.setData(self.idModel.index(0, 2), QVariant(noAkhir))
          else:
              self.idModel.insertRow(0)
              self.idModel.setData(self.idModel.index(0, 0), QVariant(kode))
              self.idModel.setData(self.idModel.index(0, 1), QVariant("pelanggan"))
              self.idModel.setData(self.idModel.index(0, 2), QVariant("1"))
              self.idModel.setData(self.idModel.index(0, 3), QVariant(per))
              self.idModel.setData(self.idModel.index(0, 4), QVariant(""))
              noAkhir = 1
          self.idModel.submitAll()
          if not QSqlDatabase.database().commit():
            raise DatabaseError
          rback = False
      finally:
          if rback:
              if not QSqlDatabase.database().rollback():
                  raise DatabaseError        
      id = "CUS"+per+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      return id
      
  def validasi(self):
      valid = True
      for lineEdit in self.lineedits:
           if lineEdit.text().isEmpty():
               valid = False
      return valid

if __name__ == "__main__":
    import sys
    app = QApplication(sys.argv)
    form = PelangganEdit()
    form.show()
    app.exec_()
